package com.bbva.general.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.bbva.general.entities.Centro;
import com.bbva.general.entities.CentroSuperior;
import com.bbva.general.entities.Feriado;
import com.bbva.general.entities.Oficina;
import com.bbva.general.entities.Producto;
import com.bbva.general.entities.Territorio;
import com.bbva.general.entities.Tipo_Cambio;
import com.bbva.general.entities.Ubigeo;
import com.bbva.general.util.ServiceContextSpring;

public class TablaGeneral {
	private Logger log = Logger.getLogger(this.getClass());
	private static final String NAME_CONTEXT_DATA_SOURCE = "dataSource";
	private static final String QUERY_BUSCAR_TERRITORIO_POR_OFICINA = "SELECT TT.CODIGO_TERRITORIO,TT.DESCRIPCION_TERRITORIO "
			+ "FROM IITG.TERRITORIO TT INNER JOIN IITG.OFICINAXTERRITORIO OFT ON TT.CODIGO_TERRITORIO=OFT.CODIGO_TERRITORIO " + "where OFT.CODIGO_OFICINA = ?";
	
	private static final String QUERY_BUSCAR_TIPO_CAMBIO = "SELECT TCMIN,TCMAX,TCPROMEDIO,DIVISA,TIPO from IITG.TIPO_CAMBIO WHERE FECHA = ? and TIPO = ? and divisa = ?";	
	private static String QUERY_GET_TIPOCAMBIO_LISTADO = "SELECT TCMIN,TCMAX,TCPROMEDIO,DIVISA,TIPO from IITG.TIPO_CAMBIO " ;
	private static final String QUERY_BUSCAR_TERRITORIO_POR_OFICINA_NE = "select codigo_oficina as CODIGO_TERRITORIO,nombre_oficina as DESCRIPCION_TERRITORIO "
		+ "FROM IITG.formato50 " + "where CODIGO_OFICINA = ?";
	private static final String QUERY_LISTAR_OFICINAS_POR_TERRITORIO = "SELECT CODIGO_OFICINA "
			+ "FROM IITG.OFICINAXTERRITORIO " + "WHERE CODIGO_TERRITORIO= ?";
	private static final String QUERY_GET_CENTRO_COSTO = "SELECT NOMBRE_OFICINA  FROM IITG.FORMATO50 WHERE CODIGO_ENTIDAD = '0011' AND CODIGO_OFICINA =?";

	private static final String QUERY_GET_DEPARTAMENTO = "SELECT IDUBIGEO,DESCRIPCION FROM IITG.UBIGEO WHERE SUBSTR(IDUBIGEO,3,7) = '00000' AND upper(DESCRIPCION) like '%'||upper(?)||'%'";
	private static final String QUERY_GET_DEPARTAMENTO_LISTADO = "SELECT IDUBIGEO,DESCRIPCION FROM IITG.UBIGEO WHERE SUBSTR(IDUBIGEO,3,7) = '00000'";

	private static final String QUERY_GET_PROVINCIA = "SELECT IDUBIGEO,DESCRIPCION FROM IITG.UBIGEO WHERE SUBSTR(IDUBIGEO,5,7) = '000' AND ( SUBSTR(IDUBIGEO,3,7) <> '00000' ) AND SUBSTR(IDUBIGEO,1,2)=SUBSTR(?,1,2) AND upper(DESCRIPCION) like '%'||upper(?)||'%'";
	private static final String QUERY_GET_PROVINCIA_LISTADO = "SELECT IDUBIGEO,DESCRIPCION FROM IITG.UBIGEO WHERE SUBSTR(IDUBIGEO,5,7) = '000' AND ( SUBSTR(IDUBIGEO,3,7) <> '00000' ) ";

	private static final String QUERY_GET_DISTRITO = "SELECT IDUBIGEO,DESCRIPCION FROM IITG.UBIGEO WHERE (SUBSTR(IDUBIGEO,3,7) <> '00000' AND SUBSTR(IDUBIGEO,5,7) <> '000') AND SUBSTR(IDUBIGEO,1,4)=SUBSTR(?,1,4) AND upper(DESCRIPCION) like '%'||upper(?)||'%'";
	private static final String QUERY_GET_DISTRITO_LISTADO = "SELECT IDUBIGEO,DESCRIPCION FROM IITG.UBIGEO WHERE (SUBSTR(IDUBIGEO,3,7) <> '00000' AND SUBSTR(IDUBIGEO,5,7) <> '000') ";

	//private static final String QUERY_GET_PRODUCTO = "SELECT UNIQUE TID_PRODUCTO, TXT_PRODUCTO_DESCRIPCION FROM IITG.PRODUCTO ";
	private static final String QUERY_GET_PRODUCTO = "SELECT UNIQUE TID_PRODUCTO, TXT_PRODUCTO_DESCRIPCION FROM IITG.PRODUCTO WHERE TID_PRODUCTO =?";
	private static final String QUERY_GET_SUBPRODUCTO = "SELECT UNIQUE TID_PRODUCTO, TID_SUBPRODUCTO, TXT_PRODUCTO_DESCRIPCION, TXT_SUBPRODUCTO_DESCRIPCION FROM IITG.PRODUCTO WHERE TID_PRODUCTO =? AND TID_SUBPRODUCTO =? ";
	private static final String QUERY_GET_PRODUCTO_LISTADO = "SELECT UNIQUE TID_PRODUCTO, TXT_PRODUCTO_DESCRIPCION, TID_SUBPRODUCTO, TXT_SUBPRODUCTO_DESCRIPCION  FROM IITG.PRODUCTO";
	private static final String QUERY_GET_SUBPRODUCTO_LISTADO = "SELECT UNIQUE TID_SUBPRODUCTO, TXT_SUBPRODUCTO_DESCRIPCION  FROM IITG.PRODUCTO";
	private static final String QUERY_GET_PRODUCTO_DETALLE = "SELECT UNIQUE TID_PRODUCTO, TXT_PRODUCTO_DESCRIPCION, TID_SUBPRODUCTO, TXT_SUBPRODUCTO_DESCRIPCION  FROM IITG.PRODUCTO";
	
	
	private static final String QUERY_GET_OFICINA_LISTADO = 
		" select cc.codigo_oficina, cc.nombre_oficina,"
		+" decode(cc.oficina_baja, 'S',2,1) cod_estado,"
		+" decode(cc.oficina_baja, 'S','Inactivo','Activo') desc_estado,"
		+" (select idubigeo from ubigeo u2 where u2.iddepartamento = u.iddepartamento and u2.idprovincia = '00' and u2.iddistrito = '000') id_departamento,"
		+" (select u2.descripcion from ubigeo u2 where u2.iddepartamento = u.iddepartamento and u2.idprovincia = '00' and u2.iddistrito = '000') desc_departamento,"	
		+" (select idubigeo from ubigeo u1 where u1.iddepartamento = u.iddepartamento and u1.idprovincia = u.idprovincia and u1.iddistrito = '000') id_provincia,"
		+" (select u1.descripcion from ubigeo u1 where u1.iddepartamento = u.iddepartamento and u1.idprovincia = u.idprovincia and u1.iddistrito = '000') desc_provincia,"
		+" cc.codigo_localidad id_distrito,"
		+" u.descripcion desc_distrito,"
		+" (select t.codigo_territorio from IITG.oficinaxterritorio t where t.codigo_oficina = cc.codigo_oficina) cod_territorio,"
		+" (select t.descripcion_territorio from IITG.territorio t where t.CODIGO_TERRITORIO = cc.codigo_oficina) desc_territorio,"
		+" trim(trim(cc.iniciales_direccion1) || ' ' || trim(cc.direccion_oficina) || ' ' || trim(cc.iniciales_direccion2) || ' ' || trim(cc.detalle_direccion) || ' ' || trim(cc.nro_exterior) || ' ' || trim(cc.nro_interior)) direccion " 
		+" from formato50 cc "
		+" inner join ubigeo u "
		+" on u.idubigeo = cc.codigo_localidad "
		+" where "
		+" (? is null or cc.codigo_oficina = ?) "
		+" and (? is null or upper(cc.nombre_oficina) like '%'||upper(?)||'%') ";
	
	private static final String QUERY_GET_TERRITORIO_LISTADO = "SELECT distinct codigo_territorio, descripcion_territorio "
		+ " FROM IITG.TERRITORIO ";

	/**
	 * Busca todas las oficinas que pertenecen a un territorio
	 * 
	 * @param codTerritorio
	 * @return
	 * @throws Exception
	 */
	public String[] getCentroCostosPorTerritorio(String codTerritorio)
			throws Exception {
		List<String> listaOficinas = null;
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);

		try {
			PreparedStatement statement = source.getConnection()
					.prepareStatement(QUERY_LISTAR_OFICINAS_POR_TERRITORIO);
			statement.setString(1, codTerritorio);
			ResultSet result = statement.executeQuery();
			listaOficinas = new ArrayList<String>();
			while (result.next()) {
				listaOficinas.add(result.getString("CODIGO_OFICINA"));
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return listaOficinas.toArray(new String[0]);
	}

	/**
	 * Buscar un territorio
	 * 
	 * @param codOficina
	 * @return territorio
	 * @throws Exception
	 */
	public Territorio getTerritorio(String codOficina) throws Exception {
		Territorio territorio = null;
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);

		try {
			PreparedStatement statement = source.getConnection()
					.prepareStatement(QUERY_BUSCAR_TERRITORIO_POR_OFICINA);
			statement.setString(1, codOficina);
			ResultSet result = statement.executeQuery();
			
			while (result.next()) {
				territorio = new Territorio();
				territorio.setCodigoTerritorio(result
						.getString("CODIGO_TERRITORIO"));
				territorio.setDescripcionTerritorio(result
						.getString("DESCRIPCION_TERRITORIO"));
			}
			
			/*if(territorio==null){
				PreparedStatement statement2 = source.getConnection()
				.prepareStatement(QUERY_BUSCAR_TERRITORIO_POR_OFICINA_NE);
				statement2.setString(1, codOficina);
				ResultSet result2 = statement2.executeQuery();
				
				while (result2.next()) {
					territorio = new Territorio();
					territorio.setCodigoTerritorio(result2
							.getString("CODIGO_TERRITORIO"));
					territorio.setDescripcionTerritorio(result2
							.getString("DESCRIPCION_TERRITORIO"));
				
				}
				statement2.close();
			}*/
				
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return territorio;
	}
	
	/**
	 * Buscar Tipo de Cambio
	 * 
	 * @param fecha,tipo,divisa
	 * @return Tipo_Cambio
	 * @throws Exception
	 */	
	public Tipo_Cambio getTipoCambio(String fecha,String tipo,String divisa) throws Exception {
		Tipo_Cambio  tipocambio = null;
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);

		try {
			PreparedStatement statement = source.getConnection()
					.prepareStatement(QUERY_BUSCAR_TIPO_CAMBIO);
			statement.setString(1, fecha);
			statement.setString(2, tipo);
			statement.setString(3, divisa);
			ResultSet result = statement.executeQuery();
			
			while (result.next()) {
				tipocambio = new Tipo_Cambio();
				tipocambio.setTcMin(result
						.getString("tcMin"));
				tipocambio.setTcMax(result
						.getString("tcMax"));
				tipocambio.setTcPromedio(result
						.getString("tcPromedio"));	
				tipocambio.setDivisa(result
						.getString("divisa"));	
				tipocambio.setTipo(result
						.getString("tipo"));	
			}			
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return tipocambio;
	}
	/**
	 *@return listado de tipo de cambio
	 *
	 */
	 public Tipo_Cambio [] getTipoCambioListado(String fecha,String tipo,String divisa) throws Exception{
		 Tipo_Cambio tipoCambio = null;
		 List<Tipo_Cambio> tiposDeCambio = new ArrayList<Tipo_Cambio>();
		 DataSource source = (DataSource) ServiceContextSpring
		 			.getBean(NAME_CONTEXT_DATA_SOURCE);
		 String QUERY_GET_TIPOCAMBIO_LISTADO_LOCAL = "";
		 try {
			 if((tipo!="")&&(divisa!="")){
				 QUERY_GET_TIPOCAMBIO_LISTADO_LOCAL = QUERY_GET_TIPOCAMBIO_LISTADO + " WHERE FECHA = '"+fecha+"' AND TIPO = '"+tipo+"' AND DIVISA = '"+divisa+"'";
			 }else if(tipo!=""){
				 QUERY_GET_TIPOCAMBIO_LISTADO_LOCAL = QUERY_GET_TIPOCAMBIO_LISTADO + " WHERE FECHA = '"+fecha+ "' AND TIPO = '"+tipo+"'";
			 }else if(divisa!=""){
				 QUERY_GET_TIPOCAMBIO_LISTADO_LOCAL = QUERY_GET_TIPOCAMBIO_LISTADO + " WHERE FECHA = '"+fecha+ "' AND DIVISA = '"+divisa+"'";
			 }else{
				 QUERY_GET_TIPOCAMBIO_LISTADO_LOCAL = QUERY_GET_TIPOCAMBIO_LISTADO + " WHERE FECHA = '"+fecha+"'";
			 }
			 PreparedStatement statement = source.getConnection()
				.prepareStatement(QUERY_GET_TIPOCAMBIO_LISTADO_LOCAL);
			 ResultSet result = statement.executeQuery();
			 
			 while (result.next()) {
				 tipoCambio = new Tipo_Cambio();
				 tipoCambio.setTcMax(result.getString("tcMax"));
				 tipoCambio.setTcMin(result.getString("tcMin"));
				 tipoCambio.setTcPromedio(result.getString("tcPromedio"));
				 tipoCambio.setDivisa(result.getString("divisa"));
				 tipoCambio.setTipo(result.getString("tipo"));
				 tiposDeCambio.add(tipoCambio);
			 }
			 statement.close();
		 } catch (SQLException e) {
				e.printStackTrace();
		 } finally {
				try {
					source.getConnection().close();
				} catch (SQLException e) {
					log.error("Cerrando conexion", e);
					throw new Exception("Error al realizar consulta.", e);
				}
		}
		 return tiposDeCambio.toArray(new Tipo_Cambio[0]);
	 }
	/**
	 * 
	 * @throws Exception
	 * @return listado de territorios
	 */
	public Territorio[] getTerritorioListado() throws Exception {
		Territorio territorio = null;
		List<Territorio> territorios = new ArrayList<Territorio>();
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);

		try {
			PreparedStatement statement = source.getConnection()
					.prepareStatement(QUERY_GET_TERRITORIO_LISTADO);
			ResultSet result = statement.executeQuery();
			while (result.next()) {
				territorio = new Territorio();
				territorio.setCodigoTerritorio(result
						.getString("CODIGO_TERRITORIO"));
				territorio.setDescripcionTerritorio(result
						.getString("DESCRIPCION_TERRITORIO"));
				territorios.add(territorio);
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return territorios.toArray(new Territorio[0]);
	}


	public Oficina[] getOficinas(String codOficina, String descOficina)
	throws Exception {
		// QUERY_GET_OFICINA_LISTADO
		
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);
		List<Oficina> oficinas = new ArrayList<Oficina>();
		try {
		
			PreparedStatement statement = source.getConnection()
					.prepareStatement(QUERY_GET_OFICINA_LISTADO);
			statement.setString(1, codOficina);
			statement.setString(2, codOficina);
			statement.setString(3, descOficina);
			statement.setString(4, descOficina);
			ResultSet result = statement.executeQuery();
			while (result.next()) {
				Oficina oficina = new Oficina();
				oficina.setEstado(result.getString("DESC_ESTADO"));
				oficina.setCodEstado(result.getString("COD_ESTADO"));
				oficina.setNombre(result.getString("NOMBRE_OFICINA"));
				oficina.setDireccion(result.getString("DIRECCION"));
				Ubigeo departamento = new Ubigeo();
				departamento.setIDUbigeo(result.getString("ID_DEPARTAMENTO"));
				departamento.setDescripcion(result
						.getString("DESC_DEPARTAMENTO"));
				oficina.setDepartamento(departamento);
		
				Ubigeo provincia = new Ubigeo();
				provincia.setIDUbigeo(result.getString("ID_PROVINCIA"));
				provincia.setDescripcion(result.getString("DESC_PROVINCIA"));
				oficina.setProvincia(provincia);
		
				Ubigeo distrito = new Ubigeo();
				distrito.setIDUbigeo(result.getString("ID_DISTRITO"));
				distrito.setDescripcion(result.getString("DESC_DISTRITO"));
				oficina.setDistrito(distrito);
		
				Territorio territorio = new Territorio();
				territorio.setCodigoTerritorio(result
						.getString("COD_TERRITORIO"));
				territorio.setDescripcionTerritorio(result
						.getString("DESC_TERRITORIO"));
				oficina.setTerritorio(territorio);
				oficinas.add(oficina);
			}
			statement.close();
		
		} catch (SQLException e) {
			log.error("Consulta centro de costos", e);
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return oficinas.toArray(new Oficina[0]);
		}

	/**
	 * Buscar un Centro de costos
	 * 
	 * @param codOficina
	 * @return cadena con nombre de centro de costo
	 * @throws Exception
	 */
	public String getCentroCosto(String codOficina) throws Exception {
		String nomOficina = null;
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);

		try {

			PreparedStatement statement = source.getConnection()
					.prepareStatement(QUERY_GET_CENTRO_COSTO);
			statement.setString(1, codOficina);
			ResultSet result = statement.executeQuery();
			if (result.next()) {
				nomOficina = result.getString("NOMBRE_OFICINA");
			}
			statement.close();

		} catch (SQLException e) {
			log.error("Consulta centro de costos", e);
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return nomOficina;
	}

	/**
	 * @return ubigeo
	 */
	public Ubigeo[] getDepartamentoListado() throws Exception {
		return getUbigeoListado(QUERY_GET_DEPARTAMENTO_LISTADO, null, null);
	}

	public Ubigeo[] getDepartamento(String descripcion) throws Exception {
		return getUbigeoListado(QUERY_GET_DEPARTAMENTO, null, descripcion);
	}

	public Ubigeo[] getProvinciaListado() throws Exception {
		return getUbigeoListado(QUERY_GET_PROVINCIA_LISTADO, null, null);
	}

	public Ubigeo[] getProvincia(String IDUbigeo, String descripcion)
			throws Exception {
		if (IDUbigeo == null)
			throw new Exception("Error : \"IDUbigeo\"  es nulo.");

		if (!validaIDUbigeoDepartamento(IDUbigeo)) {
			throw new Exception(
					"Error : \"IDUbigeo\"  de departamento incorrecto.");
		}
		return getUbigeoListado(QUERY_GET_PROVINCIA, IDUbigeo, descripcion);
	}

	public Ubigeo[] getDistritoListado() throws Exception {
		return getUbigeoListado(QUERY_GET_DISTRITO_LISTADO, null, null);
	}

	public Ubigeo[] getDistrito(String IDUbigeo, String descripcion)
			throws Exception {
		if (IDUbigeo == null)
			throw new Exception("Error : \"IDUbigeo\"  es nulo.");

		if (!validaIDUbigeoProvincia(IDUbigeo)) {
			throw new Exception(
					"Error : \"IDUbigeo\"  de provincia incorrecto.");
		}
		return getUbigeoListado(QUERY_GET_DISTRITO, IDUbigeo, descripcion);
	}

	public Producto getProducto(String productoId) throws Exception {
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);
		Producto producto = null;
		//String QUERY_GET_PRODUCTO_LOCAL = "";
		//QUERY_GET_PRODUCTO_LOCAL=QUERY_GET_PRODUCTO + " WHERE TID_PRODUCTO = '"+productoId+"'";
		try {
			PreparedStatement statement = source.getConnection()
					.prepareStatement(QUERY_GET_PRODUCTO);
			statement.setString(1, productoId);
			ResultSet result = statement.executeQuery();
		
			if (result.next()) {
				producto = new Producto();
				producto.setProductoId(result.getString("TID_PRODUCTO"));
				producto.setProductoNombre(result.getString("TXT_PRODUCTO_DESCRIPCION"));
			}
		
			statement.close();
		} catch (SQLException e) {
			log.error("Consulta PRODUCTO : ", e);
			throw new Exception("Error al realizar consulta.");
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return producto;
		}
	
	public Producto getSubProducto(String productoId, String SubProductoId) throws Exception {
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);
		Producto producto = null;
		try {
			PreparedStatement statement = source.getConnection()
					.prepareStatement(QUERY_GET_SUBPRODUCTO);
			statement.setString(1, productoId);
			statement.setString(2, SubProductoId);
			ResultSet result = statement.executeQuery();

			if (result.next()) {
				producto = new Producto();
				producto.setProductoId(result.getString("TID_PRODUCTO"));
				producto.setSubProductoId(result.getString("TID_SUBPRODUCTO"));
				producto.setProductoNombre(result
						.getString("TXT_PRODUCTO_DESCRIPCION"));
				producto.setSubProductoNombre(result
						.getString("TXT_SUBPRODUCTO_DESCRIPCION"));
			}

			statement.close();
		} catch (SQLException e) {
			log.error("Consulta PRODUCTO : ", e);
			throw new Exception("Error al realizar consulta.");
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return producto;

	}
	
	public Producto[] getProductoListado() throws Exception {
		Producto producto = null;
		List<Producto> productos = new ArrayList<Producto>();
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);
		try {
			
			PreparedStatement statement = source.getConnection()
					.prepareStatement(QUERY_GET_PRODUCTO_LISTADO);
			ResultSet result = statement.executeQuery();
			
			while (result.next()) {
				producto = new Producto();
				producto.setProductoId(result.getString("TID_PRODUCTO"));
				producto.setSubProductoId(result.getString("TID_SUBPRODUCTO"));
				producto.setProductoNombre(result
						.getString("TXT_PRODUCTO_DESCRIPCION"));
				producto.setSubProductoNombre(result
						.getString("TXT_SUBPRODUCTO_DESCRIPCION"));
				productos.add(producto);
			}

			statement.close();
		} catch (SQLException e) {
			log.error("Consulta PRODUCTO : ", e);
			throw new Exception("Error al realizar consulta.");
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return productos.toArray(new Producto[0]);
	}


	public Producto[] getSubProductoListado(String productoId) throws Exception {
		Producto producto = null;
		List<Producto> productos = new ArrayList<Producto>();
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);
		String QUERY_GET_SUBPRODUCTO_LISTADO_LOCAL = QUERY_GET_SUBPRODUCTO_LISTADO+ " WHERE TID_PRODUCTO = '"+productoId+"'";
		try {
					
			PreparedStatement statement = source.getConnection()
					.prepareStatement(QUERY_GET_SUBPRODUCTO_LISTADO_LOCAL);
			ResultSet result = statement.executeQuery();
			
			while (result.next()) {
				producto = new Producto();
				producto.setSubProductoId(result.getString("TID_SUBPRODUCTO"));
				producto.setSubProductoNombre(result
						.getString("TXT_SUBPRODUCTO_DESCRIPCION"));
				productos.add(producto);
			}
			statement.close();
		} catch (SQLException e) {
			log.error("Consulta PRODUCTO : ", e);
			throw new Exception("Error al realizar consulta.");
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return productos.toArray(new Producto[0]);
	}

	public Producto[] getProductoDetalle(String productoId,String subProductoId, String descProducto , String descSubProducto) throws Exception {
		Producto producto = null;
		List<Producto> productos = new ArrayList<Producto>();
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);
		String QUERY_GET_PRODUCTO_LISTADO_LOCAL = "";
		try {
			if(productoId!="" && subProductoId!="" && descProducto!="" && descSubProducto!="" ){
				QUERY_GET_PRODUCTO_LISTADO_LOCAL = QUERY_GET_PRODUCTO_DETALLE + " WHERE TID_PRODUCTO = '"+productoId+"' AND TID_SUBPRODUCTO = '"+subProductoId+"' AND TXT_PRODUCTO_DESCRIPCION = '"+descProducto+"' AND TXT_SUBPRODUCTO_DESCRIPCION= '"+descSubProducto+"'";
			}else if(productoId!="" && subProductoId=="" && descProducto=="" && descSubProducto=="" ){
				QUERY_GET_PRODUCTO_LISTADO_LOCAL = QUERY_GET_PRODUCTO_DETALLE + " WHERE TID_PRODUCTO = '"+productoId+"'";
			}else if(productoId=="" && subProductoId!="" && descProducto=="" && descSubProducto=="" ){
				QUERY_GET_PRODUCTO_LISTADO_LOCAL = QUERY_GET_PRODUCTO_DETALLE + " WHERE TID_SUBPRODUCTO = '"+subProductoId+"'";
			}else if(productoId=="" && subProductoId=="" && descProducto!="" && descSubProducto=="" ){
				QUERY_GET_PRODUCTO_LISTADO_LOCAL = QUERY_GET_PRODUCTO_DETALLE + " WHERE TXT_PRODUCTO_DESCRIPCION = '"+descProducto+"'";
			}else if(productoId=="" && subProductoId=="" && descProducto=="" && descSubProducto!="" ){
				QUERY_GET_PRODUCTO_LISTADO_LOCAL = QUERY_GET_PRODUCTO_DETALLE + " WHERE TXT_SUBPRODUCTO_DESCRIPCION= '"+descSubProducto+"'";
			}else if(productoId!="" && subProductoId=="" && descProducto!="" && descSubProducto=="" ){
				QUERY_GET_PRODUCTO_LISTADO_LOCAL = QUERY_GET_PRODUCTO_DETALLE + " WHERE TID_PRODUCTO = '"+productoId+"' AND TXT_PRODUCTO_DESCRIPCION= '"+descProducto+"'";
			}else if(productoId!="" && subProductoId!="" && descProducto=="" && descSubProducto=="" ){
				QUERY_GET_PRODUCTO_LISTADO_LOCAL = QUERY_GET_PRODUCTO_DETALLE + " WHERE TID_PRODUCTO = '"+productoId+"' AND TID_SUBPRODUCTO = '"+subProductoId+"'";
			}else if(productoId=="" && subProductoId=="" && descProducto!="" && descSubProducto!="" ){
				QUERY_GET_PRODUCTO_LISTADO_LOCAL = QUERY_GET_PRODUCTO_DETALLE + " WHERE TXT_PRODUCTO_DESCRIPCION = '"+descProducto+"' AND TXT_SUBPRODUCTO_DESCRIPCION= '"+descSubProducto+"'";
			}else if(productoId=="" && subProductoId!="" && descProducto=="" && descSubProducto!="" ){
				QUERY_GET_PRODUCTO_LISTADO_LOCAL = QUERY_GET_PRODUCTO_DETALLE + " WHERE TID_SUBPRODUCTO = '"+subProductoId+"' AND TXT_SUBPRODUCTO_DESCRIPCION= '"+descSubProducto+"'";
			}else if(productoId!="" && subProductoId=="" && descProducto=="" && descSubProducto!="" ){
				QUERY_GET_PRODUCTO_LISTADO_LOCAL = QUERY_GET_PRODUCTO_DETALLE + " WHERE TID_PRODUCTO = '"+productoId+"' AND TXT_SUBPRODUCTO_DESCRIPCION= '"+descSubProducto+"'";
			}else
			{
				QUERY_GET_PRODUCTO_LISTADO_LOCAL = QUERY_GET_PRODUCTO_DETALLE;
			}
			
			PreparedStatement statement = source.getConnection()
					.prepareStatement(QUERY_GET_PRODUCTO_LISTADO_LOCAL);
			ResultSet result = statement.executeQuery();
			
			while (result.next()) {
				producto = new Producto();
				producto.setProductoId(result.getString("TID_PRODUCTO"));
				producto.setSubProductoId(result.getString("TID_SUBPRODUCTO"));
				producto.setProductoNombre(result
						.getString("TXT_PRODUCTO_DESCRIPCION"));
				producto.setSubProductoNombre(result
						.getString("TXT_SUBPRODUCTO_DESCRIPCION"));
				productos.add(producto);
			}

			statement.close();
		} catch (SQLException e) {
			log.error("Consulta PRODUCTO : ", e);
			throw new Exception("Error al realizar consulta.");
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return productos.toArray(new Producto[0]);
	}
	
		
	private Ubigeo[] getUbigeoListado(String query, String IDUbigeo,
			String descripcion) throws Exception {

		log.info("Listado Ubigeo");
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);
		List<Ubigeo> ubigeos = null;
		Ubigeo ubigeo = null;
		try {
			PreparedStatement statement = source.getConnection()
					.prepareStatement(query);
			if (IDUbigeo != null) {
				statement.setString(1, IDUbigeo);
			}
			if (IDUbigeo == null && descripcion != null) {
				statement.setString(1, descripcion);
			}
			if (IDUbigeo != null && descripcion != null) {
				statement.setString(2, descripcion);
			}
			ResultSet result = statement.executeQuery();
			ubigeos = new ArrayList<Ubigeo>();
			while (result.next()) {

				ubigeo = new Ubigeo();
				ubigeo.setIDUbigeo(result.getString("IDUBIGEO"));
				ubigeo.setDescripcion(result.getString("DESCRIPCION"));
				ubigeos.add(ubigeo);
			}
			statement.close();
		} catch (SQLException e) {
			log.error("Consulta UBIGEO : " + query, e);
			throw new Exception("Error al realizar consulta.");
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return ubigeos.toArray(new Ubigeo[0]);
	}

	private String getUbigeo(String query, String IDUbigeo) throws Exception {
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);
		String ubigeo = null;
		try {
			PreparedStatement statement = source.getConnection()
					.prepareStatement(query);
			statement.setString(1, IDUbigeo);
			ResultSet result = statement.executeQuery();

			while (result.next()) {
				ubigeo = result.getString("DESCRIPCION");
			}
			statement.close();
		} catch (SQLException e) {
			log.error("Consulta UBIGEO : " + query, e);
			throw new Exception("Error al realizar consulta.");
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return ubigeo;
	}

	private boolean validaIDUbigeoDepartamento(String IDUbigeo) {
		String regex = "^\\d{2}0{5}$";
		return Pattern.matches(regex, IDUbigeo);
	}

	private boolean validaIDUbigeoProvincia(String IDUbigeo) {
		String regex = "^\\d{4}0{3}$";
		return Pattern.matches(regex, IDUbigeo);
	}
	
	/**
	 * Retorna el listado total de oficinas
	 * 12.08.08
	 * @param 
	 * @return Arreglo de oficinas
	 * @throws Exception
	 */	
	
	public Centro[] getCentroListado(String codigoOficina) throws Exception {

		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);

		String sQueryOfListado = " select"
				+ " cc.codigo_oficina,"
				+ " cc.nombre_oficina,"
				+ " cc.oficina_baja,"
				+ " (select dep.idubigeo from IITG.ubigeo dep where dep.idubigeo like cc.codigo_estado || '00000') cod_departamento,"
				+ " (select dep.descripcion from IITG.ubigeo dep where dep.idubigeo like cc.codigo_estado || '00000') desc_departamento,"
				+ " (select prov.idubigeo from IITG.ubigeo prov where prov.idubigeo like cc.codigo_estado || cc.codigo_provincia || '000') cod_provincia,"
				+ " (select prov.descripcion from IITG.ubigeo prov where prov.idubigeo like cc.codigo_estado || cc.codigo_provincia || '000') desc_provincia,"
				+ " cc.codigo_localidad cod_distrito,"
				+ " (select dist.descripcion from ubigeo dist where dist.idubigeo like cc.codigo_localidad) desc_distrito,"
				+ " (select t.codigo_territorio from IITG.oficinaxterritorio t where t.codigo_oficina = cc.codigo_oficina) cod_territorio,"
				+ " (select DESCRIPCION_TERRITORIO from IITG.oficinaxterritorio ot inner join IITG.territorio t on ot.codigo_territorio=t.codigo_territorio where ot.codigo_oficina = cc.codigo_oficina) desc_territorio,"
				+ " cc.iniciales_direccion1," + " cc.direccion_oficina,"
				+ " cc.nro_exterior," + " cc.nro_interior,"
				+ " cc.iniciales_direccion2," + " cc.detalle_direccion,"
				+ " cc.prefijo_telefono," + " cc.numero_telefono1,"
				+ " cc.numero_telefono2," + " cc.numero_fax,"
				+ " cc.fecha_apertura_oficina," + " cc.fecha_baja_oficina,"
				+ " cc.fecha_alta_centro," 
				+ " cc.tipo_oficina,"
				+ " cc.tipo_oficina_centro"
				+ " from formato50 cc"
				+ " where ? is null or cc.codigo_oficina = ? ";

		List<Centro> oficinas = new ArrayList<Centro>();
		try {

			PreparedStatement statement = source.getConnection()
					.prepareStatement(sQueryOfListado);
			statement.setString(1, codigoOficina);
			statement.setString(2, codigoOficina);
			ResultSet result = statement.executeQuery();
			while (result.next()) {
				Centro oficina = new Centro();
				oficina.setCodigoOficina(result.getString("codigo_oficina"));
				oficina.setNombre(result.getString("nombre_oficina"));
				String sOficinaBaja = result.getString("oficina_baja");
				if (sOficinaBaja != null) {
					Boolean bOficinaBaja = sOficinaBaja == "S" ? true : false;
					oficina.setOficinaBaja(bOficinaBaja);
				}
				Ubigeo departamento = new Ubigeo();
				departamento.setIDUbigeo(result.getString("cod_departamento"));
				departamento.setDescripcion(result.getString("desc_departamento"));
				oficina.setDepartamento(departamento);
				Ubigeo provincia = new Ubigeo();
				provincia.setIDUbigeo(result.getString("cod_provincia"));
				provincia.setDescripcion(result.getString("desc_provincia"));
				oficina.setProvincia(provincia);
				Ubigeo distrito = new Ubigeo();
				distrito.setIDUbigeo(result.getString("cod_distrito"));
				distrito.setDescripcion(result.getString("desc_distrito"));
				oficina.setDistrito(distrito);
				oficina.setTipoVia(result.getString("iniciales_direccion1"));
				oficina.setNombreVia(result.getString("direccion_oficina"));
				oficina.setNumeroExterior(result.getString("nro_exterior"));
				oficina.setNumeroInterior(result.getString("nro_interior"));
				oficina.setTipZona(result.getString("iniciales_direccion2"));
				oficina.setNombreZona(result.getString("detalle_direccion"));
				oficina.setPrefijoTelefono(result.getString("prefijo_telefono"));
				oficina.setTelefono1(result.getString("numero_telefono1"));
				oficina.setTelefono2(result.getString("numero_telefono2"));
				oficina.setNumeroFax(result.getString("numero_fax"));
				oficina.setFechaApertura(result.getDate("fecha_apertura_oficina"));
				oficina.setFechaBaja(result.getDate("fecha_baja_oficina"));
				oficina.setFechaAltaCentro(result.getDate("fecha_alta_centro"));
				oficina.setTipoOficina(result.getString("tipo_oficina"));
				oficina.setTipoOficinaCentro(result.getString("tipo_oficina_centro"));
				if(result.getString("cod_territorio")!=null){
					Territorio territorio = new Territorio();
					territorio.setCodigoTerritorio(result.getString("cod_territorio"));
					territorio.setDescripcionTerritorio(result.getString("desc_territorio"));
					oficina.setTerritorio(territorio);
				}
				oficinas.add(oficina);
			}

			String sQueryCentroSuperior = "";
			sQueryCentroSuperior = "select" + " codigo_agrupacion,"
					+ " codigo_nivel," + " codigo_centro_superior"
					+ " from IITG.formato51" + " where codigo_centro = ?";
			statement = source.getConnection().prepareStatement(
					sQueryCentroSuperior);
			for (Centro oficina : oficinas) {
				statement.setString(1, oficina.getCodigoOficina());
				ResultSet result2 = statement.executeQuery();
				List<CentroSuperior> centroSuperior = new ArrayList<CentroSuperior>();
				while (result2.next()) {
					CentroSuperior cs = new CentroSuperior();
					cs.setCodigoAgrupacion(result2.getString("codigo_agrupacion"));
					cs.setCodigoNivel(result2.getString("codigo_nivel"));
					cs.setCodigoCentroSuperior(result2.getString("codigo_centro_superior"));
					centroSuperior.add(cs);
				}
				oficina.setCentroSuperiores(centroSuperior.toArray(new CentroSuperior[0]));
			}					
			
			statement.close();

		} catch (SQLException e) {
			log.error("Consulta Listado de Oficina ", e);
			e.printStackTrace();
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return oficinas.toArray(new Centro[0]);
	}
	
	/**
	 * Retorna el listado de Centros Superiores de una oficina
	 * 12.08.08 
	 * @param 
	 * @return Arreglo de oficinas
	 * @throws Exception
	 */

	public CentroSuperior[] getCentroSuperior(String codigoOficina,
			String codigoAgrupacion) throws Exception {
		List<CentroSuperior> centroSuperior = null;
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);

		String sQueryCentroSuperior = "select" + " codigo_agrupacion,"
				+ " codigo_nivel," + " codigo_centro_superior"
				+ " from IITG.formato51" + " where codigo_centro = ?"
				+ " and (? is null or UPPER(codigo_agrupacion) = ?)";

		try {
			PreparedStatement statement = source.getConnection()
					.prepareStatement(sQueryCentroSuperior);
			statement.setString(1, codigoOficina);
			statement.setString(2, codigoAgrupacion.toUpperCase().trim());
			statement.setString(3, codigoAgrupacion.toUpperCase().trim());
			ResultSet result = statement.executeQuery();
			centroSuperior = new ArrayList<CentroSuperior>();
			while (result.next()) {
				CentroSuperior cs = new CentroSuperior();
				cs.setCodigoAgrupacion(result.getString("codigo_agrupacion"));
				cs.setCodigoNivel(result.getString("codigo_nivel"));
				cs.setCodigoCentroSuperior(result
						.getString("codigo_centro_superior"));
				centroSuperior.add(cs);
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return centroSuperior.toArray(new CentroSuperior[0]);
	}

	
	/**
	 * Retorna el listado de Centros Superiores de una oficina
	 * 12.08.08 
	 * @param 
	 * @return Arreglo de oficinas
	 * @throws Exception
	 */

	public Feriado[] getFeriadoListado() throws Exception {
		List<Feriado> feriados = null;
		DataSource source = (DataSource) ServiceContextSpring
				.getBean(NAME_CONTEXT_DATA_SOURCE);
				
		String sQueryFeriado = "select"
						+ " fecha,"
						+ " descripcion,"
						+ " indicador_feriado,"
						+ " idubigeo"
						+ " from IITG.feriado";						
		
		try {
			PreparedStatement statement = source.getConnection()
					.prepareStatement(sQueryFeriado);
			//statement.setString(1, codigoOficina);
			ResultSet result = statement.executeQuery();
			feriados = new ArrayList<Feriado>();
			while (result.next()) {
				Feriado feriado = new Feriado();
				feriado.setFecha(result.getDate("fecha"));
				feriado.setDescripcion(result.getString("descripcion"));
				feriado.setIndicador(result.getString("indicador_feriado"));
				feriado.setUbigeo(result.getString("idubigeo"));
				feriados.add(feriado);
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				source.getConnection().close();
			} catch (SQLException e) {
				log.error("Cerrando conexion", e);
				throw new Exception("Error al realizar consulta.", e);
			}
		}
		return feriados.toArray(new Feriado[0]);
	}	
}
